unit StockDataDAO;

interface

uses DB, ADODB, Contnrs, SysUtils, StockDataPO, Classes, StrUtils, Dialogs,
  DataBlockAnalyze;

type
  TDaoBase = class
  protected
    MySQLConnect: TADOConnection;
    DataQuery: TADOQuery;
    RunCommandQuery: TADOQuery;
    Password: string;
    Address: string;
    DataBaseName: string;
    UserName: string;
    procedure Init(); virtual;
    function OpenQuery(ASQL: string): TADOQuery;
    procedure RunSQL(ASQL: String);
  public
    constructor Create(); overload;
    constructor Create(AUserName, APassword, ADataBaseName,
      AAddress: String); overload;
  end;

  TStockDataDAO = class(TDaoBase)
  private
    procedure OpenQueryByStockInfo(ACode: TStockInfo);
    function GetStockData(ACode: TStockInfo): TStockData;

  protected
    SSTotalStockData, SZTotalStockData: TStockData;

    procedure Init(); override;
  public
    function GetStockInfo(ACode: String): TStockInfo;
    function GetAllStockInfo(): TObjectList;
    function GetFirstSockData(ACode: TStockInfo): TStockData;
    function GetDataBlock(ABlockInfo: TStockBolckInfo): TDataBlock;
    function GetAllDataBlock(): TObjectList;
    function GetAllBlockTableName(): TStringList;
    function GetBlockRecordCount(): Integer;
    function QueryDataBlock(ATableName: String): TDataSet;
    function GetLastStockDataBlock(ACode: TStockInfo; ALength: Integer = 30)
      : TDataBlock;
  end;

const
  CONNECT_STR =
    'DRIVER={MySQL ODBC 3.51 Driver}; SERVER=%s; PORT=3306; DATABASE=%s; USER=%s; PASSWORD=%s; OPTION=3';

  SS_TOTAL_CODE = '000001.ss';

  SZ_TOTAL_CODE = '399001.SZ';

implementation

{ TStockDataDAO }

constructor TDaoBase.Create;
begin
  // TODO 1: Read from ini
  Init();
end;

constructor TDaoBase.Create(AUserName, APassword, ADataBaseName,
  AAddress: String);
begin
  UserName := AUserName;
  Password := APassword;
  DataBaseName := ADataBaseName;
  Address := AAddress;
  Init();
end;

function TStockDataDAO.GetAllBlockTableName: TStringList;
begin
  Result := TStringList.Create;
  with OpenQuery
    ('select Table_Name from select_data_index group by Table_Name') do
    while not EOF do
    begin
      Result.Add(FieldByName('Table_Name').AsString);
      Next;
    end;
end;

function TStockDataDAO.GetAllDataBlock: TObjectList;
begin
  Result := TObjectList.Create(True);
  with OpenQuery
    ('SELECT a.CODE as ACODE, a.TABLE_NAME as ATABLE_NAME , a.BLOCK as ABLOCK, b.* FROM select_data_index a Left JOIN STOCK_INFO b ON b.CODE = a.CODE')
    do
    while not EOF do
    begin
      Result.Add(TStockBolckInfo.Create(TStockInfo.Create(FieldByName('ACODE')
        .AsString, FieldByName('Name').AsString, FieldByName('TABLE_NAME')
        .AsString, FieldByName('MAXVOLUME').AsInteger, FieldByName('MINVOLUME')
        .AsInteger, FieldByName('AVGVOLUME').AsInteger),
        FieldByName('ACODE').AsString, FieldByName('Name').AsString,
        FieldByName('ATABLE_NAME').AsString, FieldByName('ABLOCK').AsString));
      Next;
    end;
end;

function TStockDataDAO.GetAllStockInfo: TObjectList;
begin
  OpenQuery('SELECT * FROM STOCK_INFO WHERE Code <> ''' + SS_TOTAL_CODE +
    ''' and Code <> ''' + SZ_TOTAL_CODE + '''');
  Result := TObjectList.Create(False);
  while not DataQuery.EOF do
  begin
    Result.Add(TStockInfo.Create(DataQuery.FieldByName('CODE').AsString,
      DataQuery.FieldByName('NAME').AsString,
      DataQuery.FieldByName('TABLE_NAME').AsString,
      DataQuery.FieldByName('MAXVOLUME').AsInteger,
      DataQuery.FieldByName('MINVOLUME').AsInteger,
      DataQuery.FieldByName('AVGVOLUME').AsInteger));
    DataQuery.Next;
  end;
end;

function TStockDataDAO.GetBlockRecordCount: Integer;
begin
  with OpenQuery('Select count(BLOCK) as acount from select_data_index') do
  begin
    Result := FieldByName('acount').AsInteger;
  end;
end;

function TStockDataDAO.GetFirstSockData(ACode: TStockInfo): TStockData;
begin
  OpenQueryByStockInfo(ACode);
  Result := GetStockData(ACode);
end;

function TStockDataDAO.GetLastStockDataBlock(ACode: TStockInfo;
  ALength: Integer): TDataBlock;
var
  sd: TStockData;
begin

  OpenQuery('SELECT * from (SELECT * FROM ' + ACode.TableName +
    ' WHERE (not open is null) AND open > 0 AND CODE = ''' + ACode.Code +
    ''' ORDER BY DATE desc limit 0,30) as c order by date');

  sd := GetStockData(ACode);
  if Assigned(sd) then
    Result := TDataBlock.Create(sd.First)
  else
    Result := nil;
end;

function TStockDataDAO.GetStockData(ACode: TStockInfo): TStockData;
var
  PreviousData: TStockData;
begin
  PreviousData := nil;
  Result := nil;
  while not DataQuery.EOF do
  begin
    PreviousData := TStockData.Create(ACode, DataQuery.FieldByName('ID')
      .AsString, DataQuery.FieldByName('DATE').AsDateTime,
      DataQuery.FieldByName('OPEN').AsFloat, DataQuery.FieldByName('High')
      .AsFloat, DataQuery.FieldByName('LOW').AsFloat,
      DataQuery.FieldByName('CLOSE').AsFloat, DataQuery.FieldByName('Volume')
      .AsInteger, DataQuery.FieldByName('CODE').AsString, PreviousData);
    if (UpperCase(ACode.Code) <> UpperCase(SS_TOTAL_CODE)) and
      (UpperCase(ACode.Code) <> UpperCase(SZ_TOTAL_CODE)) then
    begin
      if UpperCase(RightStr(ACode.Code, 2)) = 'SS' then
      begin
       // if SSTotalStockData.Get(PreviousData.Date) = nil then
       // begin
       //   ShowMessage(DateToStr(PreviousData.Date) + '-' + ACode.Code);
       // end;

        PreviousData.TotalStockData :=
          SSTotalStockData.Get(PreviousData.Date).Clone;
      end
      else
      begin
        //if SZTotalStockData.Get(PreviousData.Date) = nil then
        //begin
       ///   ShowMessage(DateToStr(PreviousData.Date) + '-' + ACode.Code);
       // end;
        PreviousData.TotalStockData :=
          SZTotalStockData.Get(PreviousData.Date).Clone;
      end;
    end;
    if not Assigned(Result) then
      Result := PreviousData;
    DataQuery.Next;
  end;
end;

function TStockDataDAO.GetStockInfo(ACode: String): TStockInfo;
begin
  with OpenQuery('SELECT * FROM STOCK_INFO WHERE Code = ''' + ACode + '''') do
  begin
    Result := TStockInfo.Create(FieldByName('CODE').AsString,
      FieldByName('NAME').AsString, FieldByName('TABLE_NAME').AsString,
      FieldByName('MAXVOLUME').AsInteger, FieldByName('MINVOLUME').AsInteger,
      FieldByName('AVGVOLUME').AsInteger)
  end;
end;

procedure TStockDataDAO.Init;
begin
  inherited Init();
  SSTotalStockData := GetFirstSockData(GetStockInfo(SS_TOTAL_CODE));
  SZTotalStockData := GetFirstSockData(GetStockInfo(SZ_TOTAL_CODE));
end;

procedure TStockDataDAO.OpenQueryByStockInfo(ACode: TStockInfo);
begin
  OpenQuery('SELECT * FROM ' + ACode.TableName +
    ' WHERE (not open is null) AND open > 0 AND CODE = ''' + ACode.Code +
    ''' ORDER BY DATE ');
end;

function TStockDataDAO.QueryDataBlock(ATableName: String): TDataSet;
begin
  Result := OpenQuery('SELECT a.CODE as ACODE, a.TABLE_NAME as ATABLE_NAME ,' +
    ' a.BLOCK as ABLOCK, b.* FROM select_data_index a Left JOIN STOCK_INFO b ' +
    'ON b.CODE = a.CODE Where a.TABLE_NAME = ''' + ATableName + '''');
end;

procedure TDaoBase.Init;
begin
  DataQuery := TADOQuery.Create(nil);
  RunCommandQuery := TADOQuery.Create(nil);
  MySQLConnect := TADOConnection.Create(nil);
  MySQLConnect.LoginPrompt := False;
  DataQuery.Connection := MySQLConnect;
  RunCommandQuery.Connection := MySQLConnect;
  MySQLConnect.ConnectionString :=
    Format(CONNECT_STR, [Address, DataBaseName, UserName, Password]);
  MySQLConnect.Open();
end;

function TDaoBase.OpenQuery(ASQL: string): TADOQuery;
begin
  DataQuery.Close;
  DataQuery.SQL.Text := ASQL;
  DataQuery.Open;
  if not DataQuery.EOF then
    DataQuery.First;
  Result := DataQuery;
end;

procedure TDaoBase.RunSQL(ASQL: String);
begin
  RunCommandQuery.Close;
  RunCommandQuery.SQL.Text := ASQL;
  RunCommandQuery.ExecSQL;
end;

function TStockDataDAO.GetDataBlock(ABlockInfo: TStockBolckInfo): TDataBlock;
var
  PreviousData: TStockData;
begin
  PreviousData := nil;
  Result := nil;
  with OpenQuery('SELECT * FROM ' + ABlockInfo.TableName + ' WHERE BLOCK = ''' +
    ABlockInfo.ID + ''' ORDER BY DATE') do
  begin
    while not EOF do
    begin
      PreviousData := TStockData.Create(ABlockInfo.StockInfo,
        FieldByName('ID').AsString, FieldByName('DATE').AsDateTime,
        FieldByName('OPEN').AsFloat, FieldByName('High').AsFloat,
        FieldByName('LOW').AsFloat, FieldByName('CLOSE').AsFloat,
        FieldByName('Volume').AsInteger, FieldByName('CODE').AsString,
        PreviousData);
      if (UpperCase(ABlockInfo.Code) <> UpperCase(SS_TOTAL_CODE)) AND
        (UpperCase(ABlockInfo.Code) <> UpperCase(SZ_TOTAL_CODE)) then
      begin
        if UpperCase(RightStr(ABlockInfo.Code, 2)) = 'SS' then
        begin
          PreviousData.TotalStockData :=
            SSTotalStockData.Get(PreviousData.Date).Clone;
        end
        else
          PreviousData.TotalStockData :=
            SZTotalStockData.Get(PreviousData.Date).Clone;
      end;
      Next;
    end;
  end;
  if Assigned(PreviousData) then
    Result := TDataBlock.Create(PreviousData.First);
end;

end.
